--EXEC SP_RMA_RECEIVE_REPORT '0', '0','SCS','06/01/2009', '06/02/2009'
CREATE PROCEDURE SP_RMA_RECEIVE_REPORT
(
	@CUSTOMER_ID    INT,
	@PROCESSSTEP_ID INT,
	@VENDOR			VARCHAR(50),
	@FROM_DATE		VARCHAR(50),
	@TO_DATE		VARCHAR(50)
)
AS
BEGIN
		CREATE TABLE #RESULTS  ( [From Customer] VARCHAR(150), [Part Number] VARCHAR(50),RMA_Receive_No VARCHAR(50),
								 [Lot Number] VARCHAR(50),[Receive To Vendor] VARCHAR(50), [Receive Qty] INT, [Receive Date] DATETIME,
								 [Disposed_Qty] INT )


		DECLARE @QUERYSTRING	VARCHAR(4000)

	SET @QUERYSTRING = N'INSERT INTO #RESULTS SELECT C_NAME, PART_NUMBER, RMA_RECEIVE_NUMBER, LOT_NUMBER, L_VENDOR_NAME, ISNull(QTY_IN,0) + (SELECT ISNULL(SUM(QTY_IN),0) FROM RMA_DISPOSE_TABLE
						WHERE RMA_RECEIVE_LOT_ID = RRLT.RMA_RECEIVE_LOT_ID ), DATE_IN, (SELECT ISNULL(SUM(QTY_IN),0) FROM RMA_DISPOSE_TABLE
						WHERE RMA_RECEIVE_LOT_ID = RRLT.RMA_RECEIVE_LOT_ID )

						  FROM RMA_Receive_Table RRT, RMA_Receive_Lot_Table RRLT, CUSTOMER C, PRODUCT P, LOCATION L
						WHERE RRT.RMA_RECEIVE_ID = RRLT.RMA_RECEIVE_ID AND RRT.CUSTOMER_ID = C.CUSTOMER_ID AND    
						RRLT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND RRLT.LOCATION_ID = L.LOCATION_ID '

	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0'  )
 	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND C.CUSTOMER_ID = ' + CONVERT(VARCHAR, @CUSTOMER_ID)
	END		
 	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'  )
 	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND P.PROCESSSTEP_ID = ' + CONVERT(VARCHAR, @PROCESSSTEP_ID)
	END
	IF ( @VENDOR IS NOT NULL AND ( @VENDOR <> '0' and @VENDOR <> 'All Vendors') )
	BEGIN
		SET @QUERYSTRING = @QUERYSTRING + ' and L_VENDOR_NAME =  ''' + @VENDOR + '''' 
	END
	IF ( @FROM_DATE IS NOT NULL AND @FROM_DATE <> '0' )
	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND DATE_IN >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TO_DATE IS NOT NULL AND @TO_DATE <> '0' )
	BEGIN
		SET @QUERYSTRING = @QUERYSTRING + +' AND DATE_IN <= '''+ CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' +''''
	END
	PRINT '@QUERYSTRING :- '+@QUERYSTRING

	EXEC (@QUERYSTRING)

	SELECT * FROM #RESULTS Order By [From Customer], [Part Number]
END

